package com.school.admin.common.utils;


import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.springframework.util.StringUtils;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.Comparator;
import java.util.List;
import java.util.Map;

/**
 * @program: school-java-mall
 * @description: Excel工具类
 * @author: sunpx
 * @create: 2021-10-12 17:03
 **/
public class ExcelUtil {

    /**
     * 设置标题样式
     *
     * @param workbook
     * @return org.apache.poi.hssf.usermodel.HSSFCellStyle
     * @author : sunpx
     * @date : 2021-10-15 16:03:01
     */
    public HSSFCellStyle styleHeader(HSSFWorkbook workbook) {
        HSSFCellStyle style = workbook.createCellStyle();
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.VIOLET.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        //上边框
        style.setBorderTop(BorderStyle.THIN);
        //下边框
        style.setBorderBottom(BorderStyle.THIN);
        //左边框
        style.setBorderLeft(BorderStyle.THIN);
        //右边框
        style.setBorderRight(BorderStyle.THIN);
        //上边框颜色
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        //下边框颜色
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        //左边框颜色
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        //右边框颜色
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setWrapText(true);
        HSSFFont font = workbook.createFont();
        //设置字号
        font.setFontHeightInPoints((short) 18);
        font.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(font);

        return style;
    }

    /**
     * @param workbook
     * @return org.apache.poi.hssf.usermodel.HSSFCellStyle
     * @author : sunpx
     * @date : 2021-10-15 16:03:25
     */
    public HSSFCellStyle styleContent(HSSFWorkbook workbook) {
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillBackgroundColor(new XSSFColor(new java.awt.Color(237, 237, 243)).getIndex());
        style.setWrapText(true);
        HSSFFont font = workbook.createFont();
        //设置字号
        font.setFontHeightInPoints((short) 15);
        style.setFont(font);

        //上边框
        style.setBorderTop(BorderStyle.THIN);
        //下边框
        style.setBorderBottom(BorderStyle.THIN);
        //左边框
        style.setBorderLeft(BorderStyle.THIN);
        //右边框
        style.setBorderRight(BorderStyle.THIN);
        //上边框颜色
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        //下边框颜色
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        //左边框颜色
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        //右边框颜色
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());

        return style;
    }

    /**
     * 标题高度
     */
    private int titleHeight = 30;

    /**
     * 内容高度
     */
    private int contentHeight = 20;

    /**
     * 宽度
     */
    private int contentWidth = (int) ((50 + 0.72) * 256);



    /**
     * 设置页脚页眉
     *
     * @param header 页眉
     * @param footer 页脚
     * @return void
     * @author : sunpx
     * @date : 2021-10-15 15:59:20
     */
    private void setHeaderOrFooter(HSSFHeader header, HSSFFooter footer, String headerText, String footerText) {
        if (header != null) {
            if (!StringUtils.isEmpty(headerText)) {
                header.setCenter(headerText);
            }
        }
        if (footer != null) {
            if (!StringUtils.isEmpty(footerText)) {
                footer.setCenter(footerText);
            }
        }
    }

    /**
     * 生成excel
     * @param correspondence 表头数据项配置
     * @param jsonConfig 生成参数配置
     * @param listData 数据
     * @return xsl格式
     */
    public byte[] generateExcel(String correspondence, String jsonConfig, List<Map<String, Object>> listData) throws IOException {

        if (StringUtils.isEmpty(correspondence) || StringUtils.isEmpty(jsonConfig)) {
            return null;
        }
        JSONArray correspondenceJson = JSONUtil.parseArray(correspondence);
        JSONObject configJson = JSONUtil.parseObj(jsonConfig);
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        HSSFHeader header = sheet.getHeader();
        HSSFFooter footer = sheet.getFooter();
        Object headerString = configJson.get("header");
        Object footerString = configJson.get("footer");
        setHeaderOrFooter(header, footer, headerString == null ? "" : (String) headerString, footerString == null ? "" : (String) headerString);
        isConfig(configJson);
        List<ExcelBean> excelBeans = correspondenceJson.toList(ExcelBean.class);
        //设置标题
        HSSFRow row = sheet.createRow(0);
        excelBeans.sort(Comparator.comparing(ExcelBean::getSort));
        for (int i = 0; i < excelBeans.size(); i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelBeans.get(i).getTitle());
            cell.setCellStyle(styleHeader(workbook));
            row.setHeightInPoints(titleHeight);
            sheet.setColumnWidth(i, contentWidth);
        }
        //设置内容
        for (int i = 1; i <= listData.size(); i++) {
            HSSFRow contentRow = sheet.createRow(i);
            contentRow.setHeightInPoints(contentHeight);
            for (int i1 = 0; i1 < excelBeans.size(); i1++) {
                HSSFCell cell = contentRow.createCell(i1);
                cell.setCellValue(String.valueOf(listData.get(i - 1).get(excelBeans.get(i1).getKey())));
                cell.setCellStyle(styleContent(workbook));
            }
        }
        workbook.write(byteArrayOutputStream);
        byte[] bytes = byteArrayOutputStream.toByteArray();
        byteArrayOutputStream.close();
        return bytes;
    }

    private void isConfig(JSONObject configJson) {
        String s1 = configJson.get("titleHeight") == null ? null : String.valueOf(configJson.get("titleHeight"));
        String s2 = configJson.get("contentHeight") == null ? null : String.valueOf(configJson.get("contentHeight"));
        String s3 = configJson.get("contentWidth") == null ? null : String.valueOf(configJson.get("contentWidth"));
        if (!StringUtils.isEmpty(s1)) {
            titleHeight= Integer.parseInt(s1);
        }
        if (!StringUtils.isEmpty(s2)) {
            titleHeight= Integer.parseInt(s2);
        }
        if (!StringUtils.isEmpty(s3)) {
            titleHeight= (int) ((Integer.parseInt(s3)+0.72)*256);
        }
    }


}
